<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Joining tables in SQLite</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="SQLite, SQL, JOINS, OUTER, INNER, JOIN, LEFT, CROSS, NATURAL">
<meta name="description" content="In this part of the SQLite tutorial, we will be
joining tables.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container2">

<div id="wide_ad" class="ltow">
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<div class="content2">


<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>Joining tables</h1>


<p>
In this part of the SQLite tutorial, we will join tables in SQLite.
</p>

<div class="big_hor">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* big_horizontal */
google_ad_slot = "2904953388";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<p>
The real power and benefits from relational databases come from joining tables.
The SQL JOIN clause combines records from two or more tables in a database.
There are basically two types of joins. INNER and OUTER.
</p>

<p>
In this part of the tutorial, we will work with Customers and Reservations tables. 
</p>

<pre class="code">
sqlite> SELECT * FROM Customers;
CustomerId  Name       
----------  -----------
1           Paul Novak 
2           Terry Neils
3           Jack Fonda 
4           Tom Willis 
</pre>

<p>
Values from the Customers table.
</p>

<pre class="code">
sqlite> SELECT * FROM Reservations;
Id  CustomerId  Day       
--  ----------  ----------
1   1           2009-22-11
2   2           2009-28-11
3   2           2009-29-11
4   1           2009-29-11
5   3           2009-02-12
</pre>

<p>
Values from the Reservations tables.
</p>


<h2>Inner joins</h2>

<p>
The inner join is the most common type of joins. It is the default join also. 
The inner join selects only those records from database tables that have matching 
values. We have three types of INNER JOINS. INNER JOIN, NATURAL INNER JOIN and 
CROSS INNER JOIN. The INNER keyword can be omitted.
</p>

<h3>INNER JOIN</h3>

<pre class="code">
sqlite> SELECT Name, Day FROM Customers AS C JOIN Reservations
   ...> AS R ON C.CustomerId=R.CustomerId;
Name         Day        
-----------  -----------
Paul Novak   2009-22-11 
Terry Neils  2009-28-11 
Terry Neils  2009-29-11 
Paul Novak   2009-29-11 
Jack Fonda   2009-02-12 
</pre>

<p>
In this SELECT statement, we have selected all customers, that have made some 
reservations. Note, that we have omitted the INNER keyword.
</p>

<p>
The statement is equivalent to the following one:
</p>

<pre class="code">
sqlite> SELECT Name, Day FROM Customers, Reservations
   ...> WHERE Customers.CustomerId = Reservations.CustomerId;
Name        Day        
----------  -----------
Paul Novak  2009-22-11 
Terry Neil  2009-28-11 
Terry Neil  2009-29-11 
Paul Novak  2009-29-11 
Jack Fonda  2009-02-12
</pre>

<p>
We get the same data.
</p>


<h3>NATURAL INNER JOIN</h3>

<p>
The NATURAL INNER JOIN automatically uses all the matching column names
for the join.
In our tables, we have a column named CustomerId in both tables.
</p>

<pre class="code">
sqlite> SELECT Name, Day FROM Customers NATURAL JOIN Reservations;
Name         Day       
-----------  ----------
Paul Novak   2009-22-11
Terry Neils  2009-28-11
Terry Neils  2009-29-11
Paul Novak   2009-29-11
Jack Fonda   2009-02-12
</pre>

<p>
We get the same data. The SQL statement is less verbose. 
</p>


<h3>CROSS INNER JOIN</h3>

<p>
The CROSS INNER JOIN combines all records
from one table with all records from another table. This type of join
has little practical value. It is also called a cartesian product of records.
</p>

<pre class="code">
sqlite> SELECT Name, Day FROM Customers CROSS JOIN Reservations;
Name         Day       
-----------  ----------
Paul Novak   2009-22-11
Paul Novak   2009-28-11
Paul Novak   2009-29-11
Paul Novak   2009-29-11
Paul Novak   2009-02-12
Terry Neils  2009-22-11
Terry Neils  2009-28-11
Terry Neils  2009-29-11
Terry Neils  2009-29-11
Terry Neils  2009-02-12
...
</pre>

<p>
The same result can be achieved with the following SQL statement:
</p>

<pre>
sqlite> SELECT Name, Day FROM Customers, Reservations;
</pre>


<h2>Outer joins</h2>

<p>
An <b>outer join</b> does not require each record in the two joined tables to have a matching record.
There are three types of outer joins. Left outer joins, right outer joins, and full outer joins.
SQLite only supports left outer joins. 
</p>

<h3>LEFT OUTER JOIN</h3>

<p>
The LEFT OUTER JOIN returns all values from the left table, even
if there is no match with the right table. It such rows, there will be NULL values. 
In other words, left outer join returns all the values from the left table, 
plus matched values from the right table. Note, that the OUTER keyword can be omitted. 
</p>

<pre class="code">
sqlite> SELECT Name, Day FROM Customers LEFT JOIN Reservations
   ...> ON Customers.CustomerId = Reservations.CustomerId;
Name         Day        
-----------  -----------
Paul Novak   2009-22-11 
Paul Novak   2009-29-11 
Terry Neils  2009-28-11 
Terry Neils  2009-29-11 
Jack Fonda   2009-02-12 
Tom Willis   NULL  
</pre>

<p>
Here we have all customers with their reservations, plus a customer, who has no
reservation. There is NULL value in his row. 
</p>

<p>
We can use the USING keyword to achieve the same result. The SQL statement 
will be less verbose.
</p>

<pre class="code">
sqlite> SELECT Name, Day FROM Customers LEFT JOIN Reservations
   ...> USING (CustomerId);
Name         Day        
-----------  -----------
Paul Novak   2009-22-11 
Paul Novak   2009-29-11 
Terry Neils  2009-28-11 
Terry Neils  2009-29-11 
Jack Fonda   2009-02-12 
Tom Willis   NULL
</pre>

<p>
Same result, with shorter SQL statement.
</p>


<h3>NATURAL LEFT OUTER JOIN</h3>

<p>
The NATURAL LEFT OUTER JOIN automatically uses all the matching column names
for the join.
</op>

<pre class="code">
sqlite> SELECT Name, Day FROM Customers NATURAL LEFT OUTER JOIN Reservations;
Name         Day       
-----------  ----------
Paul Novak   2009-22-11
Paul Novak   2009-29-11
Terry Neils  2009-28-11
Terry Neils  2009-29-11
Jack Fonda   2009-02-12
Tom Willis   NULL  
</pre>

<p>
Same result, but with fewer key strokes.
</p>

<p>
In this part of the SQLite tutorial, we were joining tables.
</p>

<div class="center"> 
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* horizontal */
google_ad_slot = "1734478269";
google_ad_width = 468;
google_ad_height = 60;
//-->
</script> 
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> 
</script> 
</div> 
<br>


<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡ 
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>

<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified January 8, 2013  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

